﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace QuanLyThongTinNhanKhau.model.DAO
{
    class NhanKhauDAO
    {
        model.DAO.SQLServiceDAO service = new SQLServiceDAO();

        internal DataTable loadTable()
        {
            DataTable dt = new DataTable();
            String sql = "select * from NHANKHAU where TinhTrang = 'Enable'";
            dt = service.getDataTable(sql);
            return dt;
        }

        internal bool checkTamTru(string str)
        {
            String sql = "select TOP(1) * from TAMTRU where MaNhanKhau = '" + str + "' and TinhTrang = 'Enable'";
            DataTable dt = service.getDataTable(sql);

            if (dt.Rows.Count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        internal bool checkTamVang(string str)
        {
            String sql = "select TOP(1) * from TAMVANG where MaNhanKhau = '" + str + "' and TinhTrang = 'Enable'";
            DataTable dt = service.getDataTable(sql);

            if (dt.Rows.Count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        internal bool checkTienAnTienSu(string str)
        {
            String sql = "select TOP(1) * from TIENANTIENSU where MaNhanKhau = '" + str + "' and TinhTrang = 'Enable'";
            DataTable dt = service.getDataTable(sql);

            if (dt.Rows.Count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        internal bool checkDaChet(string str)
        {
            String sql = "select TOP(1) * from CHUNGTU where MaNhanKhau = '" + str + "' and TinhTrang = 'Enable'";
            DataTable dt = service.getDataTable(sql);

            if (dt.Rows.Count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        internal DataTable timKiem(string maNhanKhau, string hoTen, string CMND)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from NHANKHAU where TinhTrang = 'Enable'");

            if (maNhanKhau != "")
            {
                sb.Append(" and MaNhanKhau Like N'%" + maNhanKhau + "%'");
            }

            if (hoTen != "")
            {
                sb.Append(" and HoTen LIKE N'%" + hoTen + "%'");
            }

            if (CMND != "")
            {
                sb.Append(" and CMND LIKE N'%" + CMND + "%'");
            }

            DataTable dt = service.getDataTable(sb.ToString());
            return dt;
        }

        internal bool isValidMaNhanKhau(string str)
        {
            String sql = "Select TOP(1) * from NhanKhau where MaNhanKhau = '"+str+"'";
            DataTable dt = service.getDataTable(sql);

            if (dt.Rows.Count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        internal bool newNhanKhau(string maNhanKhau, string hoTen, string trinhDo, string gioiTinh, string queQuan, string CMND, string noiThuongTru, string ngaySinh)
        {
            String sql = "insert into NHANKHAU values ('" + maNhanKhau + "',N'" + hoTen + "','" + ngaySinh + "',N'" + gioiTinh + "',N'" + CMND + "',N'" + queQuan + "',N'" + noiThuongTru + "',N'" + trinhDo + "','Enable')";
            return service.executeNonQuery(sql);
        }

        internal bool updateNhanKhau(string maNhanKhau, string hoTen, string trinhDo, string gioiTinh, string queQuan, string CMND, string noiThuongTru, string ngaySinh)
        {
            String sql = "update NHANKHAU set HoTen = N'" + hoTen + "', NgaySinh = '" + ngaySinh + "', GioiTinh = N'" + gioiTinh + "', CMND = N'" + CMND + "', QueQuan = N'" + queQuan + "', NoiThuongTru = N'" + noiThuongTru + "',TrinhDo = N'" + trinhDo + "' where MaNhanKhau = '"+maNhanKhau+"'";
            return service.executeNonQuery(sql);
        }
        
        internal bool deleteNhanKhau(string maNhanKhau)
        {
            String sql = "update NHANKHAU set TinhTrang = 'Disable' where MaNhanKhau = '"+maNhanKhau+"'";
            return service.executeNonQuery(sql);
        }
    }
}
